package com.hst.capacity.infra.persistence.jpa.setting.station;

import com.hst.capacity.domain.model.entity.franchisee.FranchiseeEntity;
import com.hst.capacity.domain.model.entity.setting.airport.AirportEntity;
import com.hst.capacity.domain.model.response.setting.AirportVO;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.CrudRepository;

import java.util.List;

public interface AirportRepository extends CrudRepository<AirportEntity, String> {

    /**
     * 通过地区CODE查找机场
     * @param code
     * @return
     */
    List<AirportEntity> findByCityCode(String code);

    AirportEntity findByIdAirport(String idAirport);

    /**
     * 通过机场三字码查找机场
     * @param iata
     * @return
     */
    List<AirportEntity> findFirstByIata(String iata);

    @Query("select new com.hst.capacity.domain.model.response.setting.AirportVO(f,a) " +
            "from FranchiseeEntity f " +
            "inner join AirportEntity a on a.cityCode = f.regionCode " +
            "where a.iata = :airportCode order by a.createTime")
    List<AirportVO> findByAirportCode(String airportCode);

    @Query("select new com.hst.capacity.domain.model.response.setting.AirportVO(f,a) " +
            "from FranchiseeEntity f " +
            "inner join AirportEntity a on a.cityCode = f.regionCode " +
            "where a.iata = :airportCode and f.regionCode = :cityCode and f.idFranchisee = :idFranchisee order by a.createTime")
    AirportVO findByAirportCodeAndCityCode(String airportCode, String cityCode, String idFranchisee);

    @Query("select new com.hst.capacity.domain.model.response.setting.AirportVO(f,a) " +
            "from FranchiseeEntity f " +
            "inner join AirportEntity a on a.cityCode = f.regionCode " +
            "where a.iata = :airportCode and f.regionCode = :cityCode order by a.createTime")
    List<AirportVO> findByAirportCodeAndCityCode(String airportCode, String cityCode);

    /**
     * 通过地区CODE查找机场
     * @param cityCode
     * @return
     */
    @Query("SELECT new com.hst.capacity.domain.model.response.setting.AirportVO(t) FROM AirportEntity t WHERE t.cityCode=:cityCode order by t.createTime")
    List<AirportVO> findByCode(String cityCode);

    /**
     * 机场名查找
     * @param cityCode
     * @param name
     * @return
     */
    @Query("SELECT t FROM AirportEntity t WHERE t.cityCode=:cityCode AND t.name like '%:name%' ")
    List<AirportEntity> findByAirPortName2(String cityCode,String name);

    /**
     * 机场名查找
     * @param cityCode
     * @param name
     * @return
     */
    @Query("SELECT t FROM AirportEntity t WHERE t.cityCode=:cityCode AND t.name like concat('%',:name,'%') AND t.name like concat('%',:name2,'%') ")
    List<AirportEntity> findByAirPortName1(String cityCode,String name,String name2);

}
